Populate The Dictionary Tables

*After all necessary Dicriontionary tables have been created in the ODS database then, populate the dictionary tables by utilizing the SSIS tools.


Use the following SQL query code as your source to populate the Dictionary_Incident_Location table.

SELECT  DISTINCT  [CaseID],

[IncidentCity],

[IncidentState],

[IncidentDate],

[Latitude],

[Longitude]       

  FROM [dbo].[US_Mass_Shooting_1966_2019_view]



*In order to populate all dictionary tables, repeat the same procedures or steps as above.

Use the following SQL query code as your source to populate the Dictionary_Incident_Place table.

SELECT DISTINCT DIL.[LocationID],

            MSV.[Title],

            MSV.[IncidentPlaceType],

            MSV.[RelationshipToIncidentLocation]

  FROM [US_Mass_Shooting_ODS_DB].[dbo].[Dictionary_Incident_Location] AS DIL

LEFT JOIN      [Cap_One_Project_Staging_DB].[dbo].[US_Mass_Shooting_1966_2019_view]AS MSV

on DIL.[CaseID] = MSV.[CaseID]

 


 Use the following SQL query code as your source to populate the Dictionary_Shooter table.

SELECT DISTINCT

      [CaseID]

      ,[ShooterAge]

      ,[ShooterSex]

      ,[ShooterRace]

  FROM [Cap_One_Project_Staging_DB].[dbo].[US_Mass_Shooting_1966_2019_view]



Use the following SQL query code as your source to populate the Dictionary_Shooter_History_Of_Mental_Illness table.

SELECT DISTINCT       DS.[ShooterID] 

                                     ,MSV.[HistoryOfMentalIllnessGeneral]

FROM                          [US_Mass_Shooter_ODS].[dbo].[Dictionary_Shooter]AS DS

LEFT JOIN                  [Cap_One_Project_Staging_DB].[dbo].[US_Mass_Shooting_1966_2019_view]AS MSV

on                                DS.[CaseID] = MSV.[CaseID]

 

Use the following SQL query code as your source to populate the Dictionary_Shooter_Status table.

SELECT DISTINCT     DS.[ShooterID]

                                  ,MSV.[ShooterCurrentStatus] 

  FROM                      [US_Mass_Shooter_ODS_DB].[dbo].[Dictionary_Shooter]AS DS

LEFT JOIN                [CAP_Project_One_Staging].[dbo].[US_Mass_Shooting_1966_2019_view]AS MSV

on                              DS.[CaseID] = MSV.[CaseID]

 

Use the following SQL query code as your source to populate the Dictionary_Shooters_Motive table.

SELECT DISTINCT    DS.[ShooterID]

                                 ,MSV.[PossibleMotiveGeneral] 

FROM                      [US_Mass_Shooter_ODS_DB].[dbo].[Dictionary_Shooter]AS DS

LEFT JOIN               [Cap_One_Project_Staging_DB].[dbo].[US_Mass_Shooting_1966_2019_view]AS MSV

on                           DS.[CaseID] = MSV.[CaseID]

 

 

Use the following SQL query code as your source to populate the Dictionary_Trageted_Victim table.

SELECT DISTINCT CaseID,

            [TargetedVictimGeneral],

            [Description]   

 FROM [Cap_One_Project_Staging_DB].[dbo].[US_Mass_Shooting_1966_2019_view]

 

 

Use the following SQL query code as your source to populate the Dictionary_Weapons table.

SELECT DISTINCT  [CaseID]
                 ,[TypeOfGunDetailed]
                 ,[TypeOfGunGeneral]
                 ,[NumberOfShotguns]
                ,[NumberOfRifles]
                ,[NumberOfHandguns]
                ,[TotalNumberOfGuns]
                ,[NumberOfAutomaticGuns]
                ,[NumberOfSemiAutomaticGuns]
 FROM [Cap_One_Project_Staging_DB].[dbo].[US_Mass_shooting_1966_2017_Stg]


Use the following SQL query code as your source to populate the tbl_Mass_Shooting_ODS table.

SELECT DISTINCT

  

     

  FROM 






https://www.youtube.com/watch?v=5nfHmh8NsHw&t=248s